package com.saver.common.db;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.saver.common.utils.PageUtils;
import com.saver.common.utils.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class JdbcTool {

    @Autowired
    private DruidDataSource druidDataSource;

    public static final char UNDERLINE = '_';

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 获取连接
     *
     * @return
     */
    private DruidPooledConnection getConnect() {
        DruidPooledConnection connection = null;
        try {
            connection = druidDataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 计数
     *
     * @param sql
     * @return
     */
    public Integer totalCount(String sql) {
        Integer count = 0;
        int from = sql.indexOf("from");

        sql = "select count(*) " + sql.substring(from);
        logger.info(sql);
        Connection connect = getConnect();
        if (connect == null) {
            return 0;
        }
        try {
            PreparedStatement preparedStatement = connect.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.next();
            count = resultSet.getInt(1);
            resultSet.close();
            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connect.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }


    /**
     * 查询
     *
     * @param sql
     * @return
     */
    public List<Map<String, Object>> list(String sql) {
        logger.info(sql);
        Connection connect = getConnect();
        List<Map<String, Object>> arrayList = new ArrayList<>();
        if (connect == null) {
            return arrayList;
        }
        try {
            PreparedStatement preparedStatement = connect.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (resultSet.next()) {

                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    String columnClassName = metaData.getColumnClassName(i + 1);
                    String columnKey = metaData.getColumnLabel(i + 1);
                    String columnName = underlineToCamel(columnKey);

                    if (columnClassName.equals("java.sql.Timestamp")) {
                        Timestamp timeStamp = resultSet.getTimestamp(columnKey);
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        map.put(columnName, sdf.format(timeStamp));
                    } else if (columnClassName.equals("java.lang.String")) {
                        map.put(columnName, resultSet.getString(columnKey));
                    } else if (columnClassName.equals("java.lang.Integer")) {
                        map.put(columnName, resultSet.getInt(columnKey));
                    } else if (columnClassName.equals("java.lang.Long")) {
                        map.put(columnName, resultSet.getLong(columnKey));
                    } else if (columnClassName.equals("java.sql.Date")) {
                        map.put(columnName, resultSet.getDate(columnKey));
                    } else if (columnClassName.equals("java.lang.Boolean")) {
                        map.put(columnName, resultSet.getBoolean(columnKey));
                    } else {
                        System.out.println(columnClassName);
                    }
                }
                arrayList.add(map);
            }

            resultSet.close();
            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connect.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return arrayList;
    }

    /**
     * 分页
     *
     * @param sql
     * @param query
     * @return
     */
    public PageUtils queryPage(String sql, Query query) {
        Integer count = totalCount(sql);
        Object sidx = query.get("sidx");
        Object order = query.get("order");
        Object limit = query.get("limit");
        Object offset = query.get("offset");
        if (sidx != null && order != null) {
            if (!order.equals("normal")) {
                sql = sql + " order by " + sidx + " " + order;
            }
        }
        if (limit != null && offset != null) {
            sql = sql + " limit " + offset + "," + limit;
        }
        List<Map<String, Object>> list = list(sql);
        return new PageUtils(list, count, query.getLimit(), query.getCurrPage());
    }

    /**
     * getOne
     *
     * @param sql
     * @return
     */
    public Map<String, Object> get(String sql) {
        List<Map<String, Object>> list = this.list(sql);
        if (list.size() == 1) {
            return list.get(0);
        }
        return null;
    }

    /**
     * 下划线格式字符串转换为驼峰格式字符串
     *
     * @param param
     * @return
     */
    public static String underlineToCamel(String param) {
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        int len = param.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = param.charAt(i);
            if (c == UNDERLINE) {
                if (++i < len) {
                    sb.append(Character.toUpperCase(param.charAt(i)));
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    /**
     * insert,update,delete执行原生sql
     *
     * @param sql
     * @return
     */
    public int execute(String sql) {
        logger.info(sql);
        Connection connect = getConnect();
        try {
            PreparedStatement preparedStatement = connect.prepareStatement(sql);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connect.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
}
